import duckdb, pandas, os
from utils.path_utils import get_project_root
from database.duckdb.duckdb_base import get_connection



# 创建 NW_FYMC 表
def create_table():
    con = get_connection()
    con.execute("""
        CREATE TABLE IF NOT EXISTS NW_FYMC (
            uuid VARCHAR(32) PRIMARY KEY,
            dwbh VARCHAR(100),
            model VARCHAR(200),
            kjnd VARCHAR(10),
            fybh VARCHAR(100),
            fymc VARCHAR(100),
            kmbh_desc VARCHAR(1000),
            status CHAR(1) DEFAULT '1',
            create_by VARCHAR(200),
            create_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            remarks VARCHAR(500)
        )
    """)
    con.close()


# 获取所有记录
def get_all_data():
    con = get_connection()
    query = "SELECT * FROM NW_FYMC"
    result = con.execute(query).fetchall()
    con.close()
    return result


# 按条件获取记录
def get_data_by_condition(p_dwbh, p_model, p_kjnd=None):
    con = get_connection()
    result = con.execute("SELECT * FROM NW_FYMC "
                         " WHERE dwbh = $p_dwbh and model = $p_model "
                         "   and  ($p_kjnd is null or kjnd = $p_kjnd) "
                         "   and status = '1'",
                         {'p_dwbh': p_dwbh, 'p_model': p_model, 'p_kjnd': p_kjnd}).fetchall()
    con.close()
    return result


def get_pagedata(p_dwbh, p_model, p_kjnd, p_page=1, p_pageSize=10):
    # 计算分页查询的起始位置
    l_offset = (p_page - 1) * p_pageSize

    con = get_connection()
    result = con.execute("SELECT * FROM NW_FYMC "
                         " WHERE dwbh = $p_dwbh and model = $p_model "
                         "   and ( kjnd = $p_kjnd or $p_kjnd is null) "
                         "   and status = '1'"
                         " ORDER BY fybh, kjnd"
                         " LIMIT $p_pageSize OFFSET $p_offset ",
                         {'p_dwbh': p_dwbh, 'p_model': p_model, 'p_kjnd': p_kjnd, 'p_pageSize': p_pageSize,
                          'p_offset': l_offset}).fetchall()

    # 获取表头（列名）
    columns = [col[0] for col in con.description]

    # 将结果转换为字典列表（键值对）
    data = [dict(zip(columns, row)) for row in result]

    # 获取数据总条数
    total_count = con.execute("SELECT COUNT(*) FROM NW_FYMC "
                              " WHERE dwbh = $p_dwbh and model = $p_model "
                              "   and ( kjnd = $p_kjnd or $p_kjnd is null) "
                              "   and status = '1'",
                              {'p_dwbh': p_dwbh, 'p_model': p_model, 'p_kjnd': p_kjnd}).fetchone()[0]

    con.close()
    return [data, total_count]


# 获取费用列表
def get_fy_list(p_dwbh, p_model, p_kjnd):
    con = get_connection()
    result = con.execute("SELECT distinct fybh, fymc FROM NW_FYMC "
                         " WHERE dwbh = $p_dwbh and model = $p_model "
                         "   and ( kjnd = $p_kjnd or $p_kjnd is null) "
                         "   and status = '1'"
                         " ORDER BY fybh, kjnd",
                         {'p_dwbh': p_dwbh, 'p_model': p_model, 'p_kjnd': p_kjnd}).fetchall()

    # 获取表头（列名）
    columns = [col[0] for col in con.description]

    # 将结果转换为字典列表（键值对）
    data = [dict(zip(columns, row)) for row in result]

    con.close()
    return data


# 获取列科目描述
def get_kmbh_desc(dwbh, model, kjnd, fybh):
    con = get_connection()
    result = con.execute("SELECT kmbh_desc FROM NW_FYMC "
                         " WHERE dwbh = ? and model = ? and  kjnd = ? "
                         "   and fybh = ? "
                         "   and status = '1'",
                         (dwbh, model, kjnd, fybh)).fetchone()[0]
    con.close()
    return result


# 从excel导入数据
def load_file(p_dwbh, p_model, p_filepath):
    try:
        # 读取一个工作表
        excel_data = pandas.read_excel(p_filepath, dtype=str)
        # df1 = excel_data.get('Sheet1')
        df1 = excel_data

        # 遍历每一行,检查数据内容
        from service.nw_project.nw_project_service import is_valid_year
        for index, row in df1.iterrows():
            # 每一行是一个 pandas Series 对象，可以通过列名访问每一列的值
            # print(f"Index: {index}")
            # print(row)  # 打印整行数据
            # 可以按列名获取特定列的值
            # r_model = row['模型']
            r_col_no = row['列编号']
            r_name = row['列名称']
            r_kjnd = row['会计年度']
            r_kmbh_desc = row['数据描述']
            # r_remarks = row['备注']
            if pandas.isna(r_kjnd) or str(r_kjnd).isspace() or not is_valid_year(r_kjnd):
                # 年度为空 或 会计年度不合法
                return 402
            # if r_model.strip() is None:
            #     # 模型为空  注：不再强制要求填入模型，缺省时取当前模型
            #     return 403
            if pandas.isna(r_col_no) or str(r_col_no).isspace():  #  or r_col_no is None or not bool(r_col_no)
                # 列编号 为空
                return 404
            if pandas.isna(r_name) or str(r_name).isspace():
                # 列名称 为空
                return 405
            if pandas.isna(r_kmbh_desc) or str(r_kmbh_desc).isspace():
                # 数据描述 为空
                return 406

        # 开始保存数据
        con = get_connection()
        con.register('temp_nw_fymc', df1)

        con.execute("""
            DELETE FROM NW_FYMC
             where dwbh = $p_dwbh and model = $p_model
               and fybh in (
                select trim(列编号)
                  from temp_nw_fymc
               );
        """, {'p_dwbh': p_dwbh, 'p_model': p_model})

        con.execute("""
            INSERT INTO NW_FYMC(uuid, dwbh, model, fybh, fymc, kjnd, kmbh_desc, create_by, remarks)
            select uuid(), $p_dwbh, COALESCE(trim(模型), $p_model), 
                   trim(列编号),
                   trim(列名称),
                   trim(会计年度),
                   trim(数据描述),
                   'admin',
                   trim(备注)
              from temp_nw_fymc;
        """, {'p_dwbh': p_dwbh, 'p_model': p_model})
        con.close()
        return 200
    except Exception as e:
        print(e)
        return 400


# 根据 ID 获取单条记录
def get_data_by_uuid(uuid):
    con = get_connection()
    query = "SELECT * FROM NW_FYMC WHERE uuid = ?"
    result = con.execute(query, (uuid,)).fetchone()
    con.close()
    return result


# 添加记录
def add_data(uuid, dwbh, model, kjnd, fybh, fymc, kmbh_desc, status, create_by, remarks):
    con = get_connection()
    con.execute("""
        INSERT INTO NW_FYMC (uuid, dwbh, model, kjnd, fybh, fymc, kmbh_desc, status, create_by, remarks)
        VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    """, (uuid, dwbh, model, kjnd, fybh, fymc, kmbh_desc, status, create_by, remarks))
    con.close()


# 更新记录
def update_data(uuid, dwbh, model, kjnd, fybh, fymc, kmbh_desc, status, create_by, remarks):
    con = get_connection()
    con.execute("""
        UPDATE NW_FYMC
        SET dwbh = ?, model = ?, kjnd = ?, fybh = ?, fymc = ?, kmbh_desc = ?, status = ?, create_by = ?, remarks = ?
        WHERE uuid = ?
    """, (dwbh, model, kjnd, fybh, fymc, kmbh_desc, status, create_by, remarks, uuid))
    con.close()


# 删除记录
def delete_data(uuid):
    con = get_connection()
    con.execute("DELETE FROM NW_FYMC WHERE uuid = ?", (uuid,))
    con.close()


# 删除记录
def clear_data(p_dwbh, p_model):
    con = get_connection()
    print("clear_data:", p_dwbh, p_model)
    con.execute("DELETE FROM NW_FYMC WHERE dwbh = ? and model = ?", (p_dwbh, p_model))
    con.close()
